﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using HAF.FoundationLib;
using HAF.FoundationLib.DBUtility;
using WareDealer.Mode;

namespace WareDealer.Helper
{
    [DisplayName("数据库助手")]
    public class DbHelper
    {
        /// <summary>
        /// 建立数据库连接
        /// </summary>
        /// <returns></returns>
        private DBProvider GetDb()
        {
            return _sqlDb ?? (_sqlDb = new DBProvider("sqlite"));
        }

        /// <summary>
        /// 数据库连接
        /// </summary>
        /// <remarks>用于获取历史库</remarks>
        /// <param name="providerName"></param>
        /// <param name="connString"></param>
        /// <returns></returns>
        private DBProvider GetDb(string providerName, string connString)
        {
            return _sqlHisDb ?? (_sqlHisDb = new DBProvider(providerName, connString));
        }

        private DBProvider _sqlDb = null;
        private DBProvider _sqlHisDb = null;

        private static DbHelper _dbhelper = null;

        private DbHelper()
        {
        }

        public static DbHelper GetInstance()
        {
            return _dbhelper ?? (_dbhelper = new DbHelper());
        }

        #region 商品基本信息处理
        /// <summary>
        /// 判断商品是否已存在
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public bool WareIsExists(string pid)
        {
            try
            {
                ProductInfo rtnobj = WareGetOne(pid);
                return (rtnobj != null);
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 新增商品信息
        /// </summary>
        /// <param name="myProduct"></param>
        public void WareInsert(ProductInfo myProduct)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                db.ExecInsert(myProduct, "ProductInfo", new string[]
                {
                    "RID", "ProductID", "SkuID", "SkuidKey", "VenderId", "ShopId", "ProductName",
                    "BrandID", "ProductBrand", "ProductAttach", "ProductIsSaled", "ProductTag", "Catalog",
                    "ProductImageWebPath",
                    "ProductImagePath", "ProductURL", "ProductCoupon", "ProductPromoMsg", "ProductDispatchMode",
                    "ProductPrice", "ProductMinPrice", "ProductMobilePrice", "ProductQQPrice", "ProductWXPrice",
                    "ProductType", "ProductPriceTrend",
                    "ProductGeneralRate", "ProductPoorRate", "ProductHotCommentTag", "ProductEvaluateCount",
                    "ProductGoodRate",
                    "CreateTime", "CreateUser", "Focus", "BEnable"
                });
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新商品信息
        /// </summary>
        /// <param name="myProduct"></param>
        public void WareUpdate(ProductInfo myProduct)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                db.ExecUpdate(myProduct, "ProductInfo", new string[]
                {
                    "ProductName", "BrandID", "ProductBrand", "ProductAttach", "ProductIsSaled",
                    "ProductTag", "Catalog", "ProductImageWebPath",
                    "ProductImagePath", "ProductURL", "ProductCoupon", "ProductPromoMsg", "ProductDispatchMode",
                    "ProductPrice", "ProductType", "ProductMobilePrice", "ProductQQPrice", "ProductWXPrice",
                    //"ProductPriceTrend", "ProductMinPrice",
                    "ProductGeneralRate", "ProductPoorRate", "ProductHotCommentTag", "ProductEvaluateCount",
                    "ProductGoodRate", "Focus"
                }, string.Format(" ProductID = '{0}'", myProduct.ProductID), null);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }
        /// <summary>
        /// 获取所有商品数据
        /// </summary>
        /// <returns></returns>
        public List<ProductInfo> GetAllWares()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = "select * from ProductInfo ";
                List<ProductInfo> rtnList = db.Query<ProductInfo>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 获取所有商品信息
        /// </summary>
        /// <returns></returns>
        public List<ProductInfo> WareGetAll()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                //string.Format("select * from GuestInfo order by GuestId limit {0} offset {0}*{1}", size, index - 1);
                //分页查询
                //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                //select * from account limit10,9
                string sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                             +
                             "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=1 order by a.CreateTime";
                List<ProductInfo> rtnList = db.Query<ProductInfo>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        //public int WareGetALLCount()
        //{
        //    DBProvider db = GetDb();
        //    try
        //    {
        //        db.Begin();
        //        //string.Format("select * from GuestInfo order by GuestId limit {0} offset {0}*{1}", size, index - 1);
        //        //分页查询
        //        //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
        //        //select * from account limit10,9
        //        string sql = "select count(*) cnt from ProductInfo where BEnable=1 order by a.CreateTime";
        //        db.QueryPage
        //        List<ProductInfo> rtnList = db.Query<ProductInfo>(sql, null);
        //        db.Commit();

        //        return rtnList;
        //    }
        //    catch (Exception ex)
        //    {
        //        Faster.XLogErr(ex.Message);
        //        //db.Rollback();
        //        return null;
        //    }
        //}

        /// <summary>
        /// 分页查询所有数据
        /// </summary>
        /// <param name="iPageSize"></param>
        /// <param name="iPage"></param>
        /// <returns></returns>
        public List<ProductInfo> WareGetALL4Page(int iPageSize, int iPage)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                //string.Format("select * from GuestInfo order by GuestId limit {0} offset {0}*{1}", size, index - 1);
                //分页查询
                //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                //select * from account limit10,9
                string sql =
                    string.Format(
                        "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                        +
                        "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=1 order by a.CreateTime limit {0} offset {0}*{1}",
                        iPageSize, iPage);
                List<ProductInfo> rtnList = db.Query<ProductInfo>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 获取所有商品集合（分页模式）
        /// </summary>
        /// <param name="typeid">类型</param>
        /// <param name="pi">分页对象</param>
        /// <returns></returns>
        public WsListY<ProductInfo> WareGetAll4Page(string typeid, PageInfo pi)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();

                string sql = "";
                if (typeid == "UnType") //未分类
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=1 and (a.ProductType = '' or a.ProductType is null) ";
                }
                else if (typeid == "down") //降价、走低
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='走低' and a.BEnable=1  ";
                }
                else if (typeid == "up") //涨价
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='涨价' and a.BEnable=1  ";
                }
                else if (typeid == "balance") //持平
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='持平' and a.BEnable=1 ";
                }
                else if (typeid == "focus") //重点关注
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.Focus=1 and a.BEnable=1 ";
                }
                else if (typeid == "trash") //回收站
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=0 ";
                }
                else if (typeid == "ALL")
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=1 ";
                }
                else
                {
                    //分页查询
                    //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                    //select * from account limit10,9
                    sql =
                        string.Format(
                            "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' else '无货' end) as ProductStock, "
                            +
                            "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductType = '{0}' and a.BEnable=1 ",
                            typeid);
                }
                WsListY<ProductInfo> rtnList = db.QueryPage<ProductInfo>(sql, "order by a.CreateTime desc ", pi, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 获取某个类别商品数据
        /// </summary>
        /// <param name="typeid"></param>
        /// <returns></returns>
        public List<ProductInfo> WareGetAll(string typeid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = "";
                if (typeid == "UnType") //未分类
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=1 and (a.ProductType = '' or a.ProductType is null) order by a.CreateTime";
                }
                else if (typeid == "down") //降价、走低
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='走低' and a.BEnable=1 order by a.CreateTime";
                }
                else if (typeid == "up") //涨价
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='涨价' and a.BEnable=1 order by a.CreateTime";
                }
                else if (typeid == "balance") //持平
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='持平' and a.BEnable=1 order by a.CreateTime";
                }
                else if (typeid == "focus") //重点关注
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.Focus=1 and a.BEnable=1 order by a.CreateTime";
                }
                else if (typeid == "trash") //回收站
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=0 order by a.CreateTime";
                }
                else if (typeid == "ALL")
                {
                    sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' else '无货' end) as ProductStock, "
                          +
                          "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=1 order by a.CreateTime";
                }
                else
                {
                    //分页查询
                    //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                    //select * from account limit10,9
                    sql =
                        string.Format(
                            "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' else '无货' end) as ProductStock, "
                            +
                            "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductType = '{0}' and a.BEnable=1 order by a.CreateTime",
                            typeid);
                }
                List<ProductInfo> rtnList = db.Query<ProductInfo>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 获取某个类别商品数据
        /// </summary>
        /// <param name="typeid">商品类型</param>
        /// <param name="wName">商品名称</param>
        /// <param name="wAttach">店铺类型</param>
        /// <returns></returns>
        public List<ProductInfo> FindWareByName(string typeid, string wName, string wAttach)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string wStr = string.IsNullOrEmpty(wName) ? "" : string.Format(" and ProductName like '%{0}%'", wName);
                string sql =
                    string.Format(
                        "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, " +
                        "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID  where a.BEnable=1 {0} order by a.CreateTime", wStr);
                List<ProductInfo> rtnList = db.Query<ProductInfo>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 获取某个商品信息
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <returns></returns>
        public ProductInfo WareGetOne(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("select * from ProductInfo where productid='{0}'", pid);
                ProductInfo rtnList = db.QueryOneRow<ProductInfo>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }
        /// <summary>
        /// 逻辑删除商品集合
        /// </summary>
        /// <param name="wares"></param>
        public void WareDelWares(List<ProductInfo> wares)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                foreach (var item in wares)
                {
                    string sql = string.Format("update ProductInfo set BEnable = 0 where rid ='{0}'", item.RID);
                    db.Exec(sql);
                }
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 逻辑删除/启用商品
        /// </summary>
        /// <param name="pid"></param>
        public void WareDelOne(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("update ProductInfo set BEnable = 0 where rid ='{0}'", pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 逻辑删除/启用商品
        /// </summary>
        /// <param name="pid"></param>
        public void WareReloadOne(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("update ProductInfo set BEnable = 1 where rid ='{0}'", pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 删除单个商品
        /// </summary>
        /// <param name="rid"></param>
        public void WareRemoveOne(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("delete from ProductInfo where productid ='{0}'", pid);
                db.Exec(sql);
                sql = string.Format("delete from PriceWebHistory where pid='{0}'", pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 清空商品库
        /// </summary>
        public void WareClear()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("delete from ProductInfo");
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        #endregion 商品基本信息处理

        #region WareType

        public List<ProductType> WareTypeGet()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                //分页查询
                //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                //select * from account limit10,9
                string sql = "select TID,Name from ProductType order by CreateTime";
                List<ProductType> rtnList = db.Query<ProductType>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 增加商品类型
        /// </summary>
        /// <param name="wType"></param>
        public void WareTypeInsert(ProductType wType)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                db.ExecInsert(wType, "ProductType", new string[] { "TID", "Name", "Description", "BEnable", "CreateTime" });
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新产品类型
        /// </summary>
        /// <param name="wType"></param>
        public void WareTypeUpdate(ProductType wType)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                db.ExecUpdate(wType, "ProductType", new string[] { "Name", "Description", "BEnable" },
                    string.Format(" TID = '{0}'", wType.TID), null);
                db.Commit();

            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新产品属性类型
        /// </summary>
        /// <param name="pid"></param>
        public void WareTypeUpdate(string pid, string typeid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("update ProductInfo set ProductType = '{1}' where ProductID = '{0}'", pid,
                    typeid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 删除商品类型
        /// </summary>
        /// <param name="tid"></param>
        public void WareTypeDelete(string tid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                //将商品中分类数据置空
                string sql = string.Format("update  Productinfo set ProductType='' where ProductType = '{0}'", tid);
                db.Exec(sql);
                //删除商品分类
                sql = string.Format("delete from ProductType where tid = '{0}'", tid);
                db.Exec(sql);
                db.Commit();

            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        #endregion WareType

        #region Price Manage

        /// <summary>
        /// 更新当前前最后价格
        /// </summary>
        public void UpdateWareLastPrice()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = "delete from PriceWebHistory where pid not in (select productid from ProductInfo);";
                db.Exec(sql);
                sql =
                "replace into ProductInfo ([RID],[ProductID],[SkuID],[SkuidKey],[VenderID],[ShopID],[Catalog],[ProductName],[ProductMinPrice],[ProductMaxPrice],[ProductLastPrice],[ProductPrice], " +
                "[ProductMobilePrice],[ProductQQPrice],[ProductWXPrice],[ProductPriceDate],[ProductPriceType],[ProductPriceTrend],[ProductType],[ProductIsSaled],[ProductStock], " +
                "[BrandID],[ProductBrand],[ProductTag],[ProductDispatchMode],[ProductEvaluateCount],[ProductGoodRate],[ProductPoorRate],[ProductGeneralRate],[ProductHotCommentTag], " +
                "[ProductCoupon],[ProductPromoMsg],[ProductImageWebPath],[ProductImagePath],[ProductURL],[ProductAttach],[Focus],[BEnable],[CreateTime],[CreateUser]) " +
                "select [RID],[ProductID],[SkuID],[SkuidKey],[VenderID],[ShopID],[Catalog],[ProductName],[ProductMinPrice],[ProductMaxPrice],b.maxdateprice,[ProductPrice], " +
                "[ProductMobilePrice],[ProductQQPrice],[ProductWXPrice],[ProductPriceDate],[ProductPriceType],[ProductPriceTrend],[ProductType],[ProductIsSaled],[ProductStock], " +
                "[BrandID],[ProductBrand],[ProductTag],[ProductDispatchMode],[ProductEvaluateCount],[ProductGoodRate],[ProductPoorRate],[ProductGeneralRate],[ProductHotCommentTag], " +
                "[ProductCoupon],[ProductPromoMsg],[ProductImageWebPath],[ProductImagePath],[ProductURL],[ProductAttach],[Focus],[BEnable],[CreateTime],[CreateUser] from ProductInfo a left join " +
                "(select max(pricedate) pricedate,pid,price as maxdateprice from PriceWebHistory where date(pricedate) < date(DateTime('now','localtime')) group by pid) b on a.productid = b.[pid]; ";
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 插入历史商品价格
        /// </summary>
        /// <param name="price">历史价格对象</param>
        public void WareHisPriceInsert(PriceWebHistory price)
        {
            if (price != null)
            {
                WareHisPriceInsert(price.PID, price.Price, price.PriceDate, price.PriceType);
            }
        }

        /// <summary>
        /// 插入历史商品价格
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <param name="price">商品价格</param>
        public void WareHisPriceInsert(string pid, double price)
        {
            WareHisPriceInsert(pid, price, DateTime.Now, "京东");
        }

        /// <summary>
        /// 插入历史商品价格
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <param name="price">商品价格</param>
        /// <param name="dt">入库时间</param>
        /// <param name="origin">数据来源 如京东、手机、超市</param>
        public void WareHisPriceInsert(string pid, double price, DateTime dt, string origin)
        {
            DBProvider db = GetDb();
            try
            {
                //先查找当天有没有相同的数据
                if (!WarePriceHistoryGetOne(pid, dt, price))
                {
                    db.Begin();
                    //string sql = string.Format("select * from PriceWebHistory");
                    //sqlite用的全球时间UTC,要用datetime()函数转换若干 localtime UTC
                    //用data_time.ToString("s");这种方法转换成 iso 8601标准字符串格式
                    string sql = string.Format("insert into PriceWebHistory (PID, Price, PriceDate, PriceType) select '{0}',{1},'{2}','{3}'",
                            pid, price, dt.ToString("s"), origin);
                    db.Exec(sql);
                    db.Commit();
                }

            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
            }
        }

        #region Price Batch
        /// <summary>
        /// 批量更新价格
        /// </summary>
        /// <param name="priceList"></param>
        public void WarePriceBatchUpdate(List<JdWarePriceCache> priceList)
        {
            if (priceList == null || priceList.Count <= 0) return;

            DBProvider db = GetDb();
            try
            {
                db.Begin();

                //JdWarePriceCache a = priceList.Find(t => t.Skuid == "862253");
                foreach (var priceItem in priceList)
                {
                    string sql = "";
                    //商品下柜处理
                    if (priceItem.WebPrice == -1)
                    {
                        sql = string.Format("update ProductInfo set BEnable = 0 where ProductID = '{0}';", priceItem.Skuid);
                    }
                    else
                    {
                        sql =
                            string.Format(
                                "update ProductInfo set ProductPrice = {0}, ProductMobilePrice = {1}, ProductQQPrice = {2}, ProductWXPrice = {3}," +
                                "ProductPriceDate = DateTime('now','localtime'), ProductPriceType = '{4}', ProductPriceTrend = '{5}',ProductMaxPrice = {6}" +
                                ",ProductMinPrice = {7} where ProductID = '{8}';",
                                priceItem.WebPrice, priceItem.AppPrice, priceItem.QqPrice, priceItem.WxPrice,
                                priceItem.MinPriceSrc, priceItem.PriceTrend,
                                priceItem.LastMaxPrice, priceItem.LastMinPrice, priceItem.Skuid);
                    }
                    db.Exec(sql);
                }

                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        private class priceCount
        {
            public int couweb;
            public int couapp;
            public int couqq;
            public int couwx;
        }
        /// <summary>
        /// 历史价格批处理
        /// </summary>
        /// <param name="priceList">商品价格集合</param>
        public void WareHisPriceBatchInsert(List<JdWarePriceCache> priceList)
        {
            if (priceList == null || priceList.Count <= 0) return;
            DBProvider db = GetDb();
            try
            {
                Stopwatch time1 = new Stopwatch();
                time1.Start();
                db.Begin();
                foreach (var item in priceList)
                {

                    string sql = "";
                    if (item.IsWebChange)
                    {
                        sql += string.Format("insert into PriceWebHistory (PID, Price, Pricedate, PriceType) select '{0}',{1}, datetime('now', 'localtime'),'京东';", item.Skuid,
                            item.WebPrice);
                    }
                    if (item.IsAppChange)
                    {
                        sql += string.Format("insert into PriceAppHistory (PID, Price, Pricedate, PriceType) select '{0}',{1}, datetime('now', 'localtime'),'手机';", item.Skuid,
                            item.AppPrice);
                    }
                    if (item.IsQqChange)
                    {
                        sql += string.Format("insert into PriceQqHistory (PID, Price, Pricedate, PriceType) select '{0}',{1}, datetime('now', 'localtime'),'QQ';", item.Skuid,
                            item.QqPrice);
                    }
                    if (item.IsWxChange)
                    {
                        sql += string.Format("insert into PriceWxHistory (PID, Price, Pricedate, PriceType) select '{0}',{1}, datetime('now', 'localtime'),'微信';", item.Skuid,
                            item.WxPrice);
                    }
                    db.Exec(sql);
                    if (item.WebPrice < 0 || item.WxPrice < 0 || item.QqPrice < 0 || item.AppPrice < 0)
                    {
                        Faster.XLogInfo(string.Format("当前商品{0}价格采集异常", item.Skuid));
                    }
                }

                db.Commit();
                time1.Stop();
                Faster.XLogDbg(string.Format("历史记录入库耗时{0}ms", time1.ElapsedMilliseconds));
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        #endregion Price Batch

        public void WarePriceUpdate(string pid, double price, WarePriceType priceSrc)
        {
            DBProvider db = GetDb();
            try
            {
                string sql = "";
                switch (priceSrc)
                {
                    case WarePriceType.WebPrice:
                        sql =
                            string.Format(
                                "update ProductInfo set ProductPrice = {0},ProductPriceDate = DateTime('now','localtime') where ProductID = '{1}';",
                                price, pid);
                        break;
                    case WarePriceType.AppPrice:
                        sql =
                            string.Format(
                                "update ProductInfo set ProductMobilePrice = {0},ProductPriceDate = DateTime('now','localtime') where ProductID = '{1}';",
                                price, pid);
                        break;
                    case WarePriceType.QQPrice:
                        sql =
                            string.Format(
                                "update ProductInfo set ProductQQPrice = {0},ProductPriceDate = DateTime('now','localtime') where ProductID = '{1}';",
                                price, pid);
                        break;
                    case WarePriceType.WxPrice:
                        sql =
                            string.Format(
                                "update ProductInfo set ProductWXPrice = {0},ProductPriceDate = DateTime('now','localtime') where ProductID = '{1}';",
                                price, pid);
                        break;
                    default:
                        break;
                }
                if (string.IsNullOrEmpty(sql))
                {
                    return;
                }
                db.Begin();
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新商品价格
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <param name="price">网页价格</param>
        public void WarePriceUpdate(string pid, double price)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "update ProductInfo set ProductPrice = {0},ProductPriceDate = DateTime('now','localtime') where ProductID = '{1}';",
                        price, pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新商品所有价格
        /// </summary>
        /// <param name="pid"></param>
        /// <param name="price1"></param>
        /// <param name="price2"></param>
        /// <param name="price3"></param>
        /// <param name="price4"></param>
        /// <param name="priceSrc"></param>
        public void WareRepositoryUpdate(string pid, double baseprice, double price1, double price2, double price3,
            double price4, string priceSrc)
        {
            string trend = "未知";
            trend = WarePriceTrend(pid, baseprice);
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                //价格对比 --> 涨价、持平、走低、未知
                string sql =
                    string.Format(
                        "update ProductInfo set ProductPrice = {0}, ProductMobilePrice = {1}, ProductQQPrice = {2}, ProductWXPrice = {3}," +
                        "ProductPriceDate = DateTime('now','localtime'), ProductPriceTrend='{4}',ProductPriceType = '{5}' where ProductID = '{6}';",
                        price1, price2, price3, price4, trend, priceSrc, pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新商品价格及库存
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <param name="price1">网页价格</param>
        /// <param name="price2">手机价格</param>
        /// <param name="price3">QQ价格</param>
        /// <param name="price4">微信价格</param>
        /// <param name="priceSrc">价格来源</param>
        /// <param name="stock">库存</param>
        public void WareRepositoryUpdate(string pid, double price1, double price2, double price3, double price4,
            string priceSrc, int stock)
        {
            string trend = "未知";
            if (stock >= 0)
            {
                trend = WarePriceTrend(pid, price1);
            }
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                //价格对比 --> 涨价、持平、走低、未知
                string sql =
                    string.Format(
                        "update ProductInfo set ProductPrice = {0}, ProductMobilePrice = {1}, ProductQQPrice = {2}, ProductWXPrice = {3}," +
                        "ProductPriceDate = DateTime('now','localtime'), ProductIsSaled={4}, ProductPriceTrend='{5}',ProductPriceType = '{6}' where ProductID = '{7}';",
                        price1, price2, price3, price4, stock, trend, priceSrc, pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新商品配送模式
        /// </summary>
        /// <param name="pid"></param>
        /// <param name="dispatch"></param>
        public void WareRepositoryUpdate(string pid, string dispatch)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format(
                    "update ProductInfo set ProductDispatchMode = '{0}' where ProductID = '{1}';", dispatch, pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 商品库存批量更新
        /// </summary>
        /// <param name="stocks"></param>
        public void WareRepositoryBatchUpdate(List<WareStock> stocks)
        {
            if (stocks == null || stocks.Count <= 0) return;

            DBProvider db = GetDb();
            try
            {
                db.Begin();
                foreach (var item in stocks)
                {
                    string sql =
                        string.Format(
                            "update ProductInfo set ProductIsSaled = {0},ProductStock = '{1}' where ProductID = '{2}';",
                            item.IsSale, item.StockName, item.Skuid);
                    db.Exec(sql);
                }

                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 获取商品价格趋势
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <param name="price">商品对比价格</param>
        /// <returns></returns>
        public string WarePriceTrend(string pid, double price)
        {
            double rtnPrice = WareLastPriceGet(pid);
            if (rtnPrice >= 0)
            {
                //价格对比 --> 涨价、持平、走低、未知
                string rtnstr = rtnPrice > price ? "走低" : (rtnPrice < price ? "涨价" : "持平");
                return rtnstr;
            }
            return "未知";
        }

        /// <summary>
        /// 获取商品当前时间点前的最后价格
        /// </summary>
        /// <remarks>用于价格趋势判断</remarks>
        /// <param name="pid">商品编号</param>
        /// <returns></returns>
        public double WareLastPriceGet(string pid)
        {
            double rtnPrice = 0;
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "select price,max(PriceDate) pricetime from PriceWebHistory where pid = {0} and DateTime(PriceDate) < DateTime('now', 'localtime');",
                        pid);
                //string sql = string.Format("select ProductPrice from ProductInfo where ProductID = '{0}';", pid);
                rtnPrice = db.QueryOneCol<double>(sql);
                db.Commit();
                return rtnPrice;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
                return -1;
            }
        }

        /// <summary>
        /// 获取商品当前时间点前最大价格
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <returns></returns>
        public double WareMaxPriceGet(string pid)
        {
            double rtnPrice = 0;
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "select pid,max(price) price from PriceWebHistory where pid = {0} and DateTime(PriceDate) < DateTime('now', 'localtime');",
                        pid);
                //string sql = string.Format("select ProductPrice from ProductInfo where ProductID = '{0}';", pid);
                rtnPrice = db.QueryOneCol<double>(sql);
                db.Commit();
                return rtnPrice;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
                return -1;
            }
        }

        /// <summary>
        /// 获取商品当前时间点前最小价格
        /// </summary>
        /// <param name="pid">商品编号</param>
        public double WareMinPriceGet(string pid)
        {
            double rtnPrice = 0;
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "select pid, min(price) minprice from PriceWebHistory where pid = {0} and DateTime(PriceDate) < DateTime('now', 'localtime');",
                        pid);
                rtnPrice = db.QueryOneCol<double>(sql);
                db.Commit();
                return rtnPrice;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
                return -1;
            }
        }

        /// <summary>
        /// 删除异常历史价格 如 0  -1
        /// </summary>
        public void WareWrongHisPriceDel()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = "delete from PriceWebHistory where price <= 0 ";
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新商品归属
        /// </summary>
        /// <param name="pid">编号</param>
        /// <param name="vid">商店编号</param>
        /// <param name="sid">厂商编号</param>
        /// <param name="cat">配送区域</param>
        public void WareShopUpdate(string pid, string vid, string sid, string cat)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "update ProductInfo set VenderId = '{0}',ShopID = '{1}',Catalog = '{2}' where ProductID = '{3}';",
                        vid, sid, cat, pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 删除商品价格
        /// </summary>
        /// <param name="id">商品序号</param>
        public void WarePriceDel(Int64 id)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("delete from PriceWebHistory where rowid = {0}", id);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 判断某个商品当天是否有相同价格存在
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <param name="dt">日期</param>
        /// <param name="price">价格</param>
        /// <returns></returns>
        public bool WarePriceHistoryGetOne(string pid, DateTime dt, double price)
        {
            bool rtnJudge = false;
            //开始日期
            DateTime dtBegin = dt.Date;
            //结束日期
            DateTime dtEnd = dt.AddDays(1).Date;
            PriceWebHistory rtnObj = WarePriceHistoryGetOne(pid, dtBegin, dtEnd, price);
            rtnJudge = rtnObj != null;
            return rtnJudge;
        }

        /// <summary>
        /// 判断某个商品当天是否有相同价格存在
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <param name="dt1">开始日期</param>
        /// <param name="dt2">结束日期</param>
        /// <param name="price">价格</param>
        /// <returns></returns>
        public PriceWebHistory WarePriceHistoryGetOne(string pid, DateTime dt1, DateTime dt2, double price)
        {
            DBProvider db = GetDb();
            try
            {
                //datetime(PriceDate,'localtime')  datetime(PriceDate,'utc')
                db.Begin();
                //分页查询
                //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                //select * from account limit10,9
                string sql =
                    string.Format("select rowid as RID,*,datetime(PriceDate) as PriceTime from PriceWebHistory " +
                                  "where pid = '{0}' and (PriceDate between '{1}' and '{2}') and Price={3}  order by PriceDate",
                        pid, dt1.ToString("s"), dt2.ToString("s"), price);
                List<PriceWebHistory> rtnList = db.Query<PriceWebHistory>(sql, null);
                db.Commit();
                if (rtnList.Count > 0)
                {
                    return rtnList[0];
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 获取商品历史价格
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <returns></returns>
        public List<PriceWebHistory> WareWebPriceHistoryGet(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                //datetime(PriceDate,'localtime')  datetime(PriceDate,'utc')
                db.Begin();
                //分页查询
                //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                //select * from account limit10,9
                //string sql = string.Format("select rowid as RID,*,datetime(PriceDate,'localtime') as PriceTime from PriceWebHistory where pid = '{0}' order by PriceDate desc", pid);
                string sql =
                    string.Format(
                        "select rowid as RID,*,datetime(PriceDate) as PriceTime from PriceWebHistory where pid = '{0}' order by PriceDate desc",
                        pid);
                List<PriceWebHistory> rtnList = db.Query<PriceWebHistory>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        public List<PriceWebHistory> WareAppPriceHistoryGet(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                //datetime(PriceDate,'localtime')  datetime(PriceDate,'utc')
                db.Begin();
                //分页查询
                //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                //select * from account limit10,9
                //string sql = string.Format("select rowid as RID,*,datetime(PriceDate,'localtime') as PriceTime from PriceWebHistory where pid = '{0}' order by PriceDate desc", pid);
                string sql =
                    string.Format(
                        "select rowid as RID,*,datetime(PriceDate) as PriceTime from PriceAppHistory where pid = '{0}' order by PriceDate desc",
                        pid);
                List<PriceWebHistory> rtnList = db.Query<PriceWebHistory>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        public List<PriceWebHistory> WareQqPriceHistoryGet(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                //datetime(PriceDate,'localtime')  datetime(PriceDate,'utc')
                db.Begin();
                //分页查询
                //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                //select * from account limit10,9
                //string sql = string.Format("select rowid as RID,*,datetime(PriceDate,'localtime') as PriceTime from PriceWebHistory where pid = '{0}' order by PriceDate desc", pid);
                string sql =
                    string.Format(
                        "select rowid as RID,*,datetime(PriceDate) as PriceTime from PriceQqHistory where pid = '{0}' order by PriceDate desc",
                        pid);
                List<PriceWebHistory> rtnList = db.Query<PriceWebHistory>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        public List<PriceWebHistory> WareWxPriceHistoryGet(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                //datetime(PriceDate,'localtime')  datetime(PriceDate,'utc')
                db.Begin();
                //分页查询
                //select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
                //select * from account limit10,9
                //string sql = string.Format("select rowid as RID,*,datetime(PriceDate,'localtime') as PriceTime from PriceWebHistory where pid = '{0}' order by PriceDate desc", pid);
                string sql =
                    string.Format(
                        "select rowid as RID,*,datetime(PriceDate) as PriceTime from PriceWxHistory where pid = '{0}' order by PriceDate desc",
                        pid);
                List<PriceWebHistory> rtnList = db.Query<PriceWebHistory>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 获取商品历史价格 只取部分，重复的价格将抛弃部分点
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public List<PriceWebHistory> WarePriceHistoryGetMore(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "select rowid as RID,*,datetime(PriceDate) as PriceTime from PriceWebHistory where pid = '{0}' "
                        + "order by PriceDate desc", pid);
                List<PriceWebHistory> rtnList = db.Query<PriceWebHistory>(sql, null);
                db.Commit();

                return rtnList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 更新价格低价和峰值数据
        /// </summary>
        /// <param name="priceList"></param>
        public void UpdateMinMaxPriceBatch(List<JdWarePriceCache> priceList)
        {
            if (priceList == null || priceList.Count <= 0) return;

            DBProvider db = GetDb();
            try
            {
                db.Begin();
                foreach (var item in priceList)
                {
                    string sql =
                        string.Format(
                            "update ProductInfo set  ProductMinPrice=(select min(price) from PriceWebHistory t "
                            + "where t.pid =  '{0}'),ProductMaxPrice=(select max(price) from PriceWebHistory t "
                            + "where t.pid =  '{0}') where ProductInfo.ProductID = '{0}'", item.Skuid);
                    db.Exec(sql);
                }

                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新商品底价
        /// </summary>
        /// <param name="pid">商品编号</param>
        public void UpdateHistoryPriceBasebyID(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "update ProductInfo set  ProductMinPrice=(select min(price) from PriceWebHistory t "
                        + "where t.pid =  '{0}') where ProductInfo.ProductID = '{0}'", pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        #endregion Price Manage

        #region SysParams

        public List<TabSysParams> GetSysParams()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("select * from SysParams");
                List<TabSysParams> myParams = db.Query<TabSysParams>(sql, null);
                db.Commit();
                //ParseSysParams(myParams);
                return myParams;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                return null;
                //db.Rollback();
            }
        }

        /// <summary>
        /// 更新系统参数集合
        /// </summary>
        /// <param name="myParams"></param>
        public void UpdateSysParams(List<TabSysParams> myParams)
        {
            foreach (var item in myParams)
            {
                UpdateSysParam(item.ParamKey, item.ParamValue);
            }
        }

        /// <summary>
        /// 添加系统参数
        /// </summary>
        /// <param name="pKey"></param>
        /// <param name="PValue"></param>
        private void InsertSysParam(string pKey, string PValue)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("select * from SysParams where ParamKey = '{0}';", pKey);
                TabSysParams rtn = db.QueryOneRow<TabSysParams>(sql);
                if (rtn == null)
                {
                    sql =
                        string.Format(
                            "insert into SysParams (ParamKey,ParamValue,ParamDescription) values ('{0}','{1}','');",
                            pKey, PValue);
                    db.Exec(sql);
                }
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 更新系统参数值
        /// </summary>
        /// <param name="pKey"></param>
        /// <param name="pValue"></param>
        public void UpdateSysParam(string pKey, string pValue)
        {
            //首先判断参数是否存在
            InsertSysParam(pKey, pValue);

            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("update SysParams set ParamValue = '{0}' where ParamKey = '{1}';", pValue,
                    pKey);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 获取商品配送区域
        /// </summary>
        /// <param name="pid">上级编号</param>
        /// <param name="level">等级</param>
        public List<DispatchArea> GetWareArea(int pid, int level)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("select * from DispatchArea where parentid = {0} and level = {1}", pid, level);
                List<DispatchArea> myAreas = db.Query<DispatchArea>(sql, null);
                db.Commit();
                return myAreas;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 根据编号获取商品区域
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public string GetWareArea(string aid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("select name from DispatchArea where id = {0}", aid);
                string name = db.QueryOneCol<string>(sql);
                db.Commit();
                return name;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                //db.Rollback();
                return null;
            }
        }

        #endregion SysParams

        /// <summary>
        /// 更新商品海报数据
        /// </summary>
        /// <param name="pid"></param>
        /// <param name="imgPath"></param>
        public void WareImageUpdate(string pid, string imgPath, string imgWebPath)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format(
                        "update ProductInfo set ProductImagePath = '{0}',ProductImageWebPath = '{1}' where ProductID = '{2}';",
                        imgPath, imgWebPath, pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        #region 商品评价信息

        /// <summary>
        /// 添加商品评价信息
        /// </summary>
        /// <param name="msgs"></param>
        public void WareMessagesAdd(List<ProductMessage> msgs)
        {
            if (msgs != null && msgs.Count > 0)
            {
                WareMessageClear(msgs[0].PID);
                foreach (var item in msgs)
                {
                    WareMessageAdd(item);
                }
            }
        }

        /// <summary>
        /// 清除商品评价信息
        /// </summary>
        /// <param name="pid"></param>
        private void WareMessageClear(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                db.Exec(string.Format("delete from ProductMessage where PID = '{0}'", pid));
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 添加评价信息
        /// </summary>
        /// <param name="msg"></param>
        public void WareMessageAdd(ProductMessage msg)
        {
            if (msg == null)
            {
                return;
            }
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                //db.Exec(string.Format("delete from ProductMessage where PID = '{0}'", msg.PID));
                db.ExecInsert(msg, "ProductMessage",
                    new string[] { "PID", "MsgType", "MsgContent", "MsgUser", "MsgUserLevel", "MsgProvince", "MsgDate" });
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 获取评价信息
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public List<ProductMessage> WareMessageGet(string pid)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("select * from ProductMessage where PID = '{0}'", pid);
                List<ProductMessage> msgs = db.Query<ProductMessage>(sql);
                db.Commit();
                return msgs;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
                return null;
            }
        }

        /// <summary>
        /// 更新评价统计信息
        /// </summary>
        /// <param name="pid">商品编号</param>
        /// <param name="count">评价总数</param>
        /// <param name="good">好评</param>
        /// <param name="general">中评</param>
        /// <param name="poor">差评</param>
        /// <param name="tag">印象</param>
        public void WareMessageUpdate(string pid, int count, double good, double general, double poor, string tag)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "update ProductInfo set ProductEvaluateCount = {0}, ProductGoodRate = {1}, ProductPoorRate = {2}," +
                        "ProductGeneralRate = {3}, ProductHotCommentTag = '{4}' where ProductID = '{5}';", count, good,
                        general, poor, tag, pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        #endregion 商品评价信息

        #region jdTypes

        /// <summary>
        /// 清空京东商品类别数据
        /// </summary>
        public void WareJDTypeClear()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                db.Exec("delete from JDWareType");
                db.Commit();

            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 添加京东商品类别
        /// </summary>
        /// <param name="_types"></param>
        public void WareJDTypeAdd(List<JDWareType> _types)
        {
            if (_types == null && _types.Count == 0)
            {
                return;
            }
            DBProvider db = GetDb();
            try
            {
                foreach (var item in _types)
                {
                    db.Begin();
                    db.ExecInsert(item, "JDWareType",
                        new string[] { "TypeID", "TypeName", "TopID", "TypeUrl", "TypeLevel", "BEnable" });
                    db.Commit();
                }

            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 获取京东商品分类数据
        /// </summary>
        /// <param name="wherestr"></param>
        /// <returns></returns>
        public List<JDWareType> GetWareJDTypes(string wherestr)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = string.Format("select * from JDWareType where BEnable = 1 {0}", wherestr);
                List<JDWareType> rtns = db.Query<JDWareType>(sql);
                db.Commit();
                return rtns;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
                return null;
            }

        }

        #endregion jdTypes

        #region 商品促销信息

        public void WarePromotionUpdate(string pid, string tag, string promo, string copon)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql =
                    string.Format(
                        "update ProductInfo set ProductTag = '{0}',ProductPromoMsg = '{1}',ProductCoupon = '{2}' where ProductID = '{3}';",
                        tag, promo, copon, pid);
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        /// <summary>
        /// 商品促销信息批量更新
        /// </summary>
        /// <param name="promots">促销信息集合</param>
        public void WareAdMessageBatchUpdate(List<AdMessageTag> promots)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = "update ProductInfo set ProductTag ='';";
                db.Exec(sql);
                foreach (var item in promots)
                {
                    sql = string.Format("update ProductInfo set ProductTag = '{0}' where ProductID = '{1}';", item.ad, item.id);
                    db.Exec(sql);

                }

                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="promots"></param>
        public void WarePromotionBatchUpdate(List<PrivilegeTag> promots)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = "update ProductInfo set ProductCoupon ='',ProductPromoMsg = '';";
                db.Exec(sql);
                foreach (var item in promots)
                {
                    sql = string.Format("update ProductInfo set ProductCoupon ='{0}',ProductPromoMsg = '{1}' where ProductID = '{2}';", item.Coupon, item.Promo, item.Sku);
                    db.Exec(sql);

                }

                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        #endregion 商品促销信息

        #region 导入历史数据
        /// <summary>
        /// 获取历史商品数据
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public List<ProductInfo> GetHisWareData(string connStr)
        {
            string conn = string.Format("Data Source={0};Pooling=true", connStr);
            DBProvider dbHis = GetDb("System.Data.SQLite", conn);
            try
            {
                dbHis.Begin();
                //不包含下柜商品
                List<ProductInfo> hisWareList = dbHis.Query<ProductInfo>("select * from ProductInfo where BEnable = 1 order by CreateTime;");
                dbHis.Commit();
                return hisWareList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                dbHis.Rollback();
                return null;
            }
        }
        /// <summary>
        /// 导入历史商品数据
        /// </summary>
        /// <param name="wares">商品数据列表</param>
        public void ImportHisWareData(List<ProductInfo> wares)
        {
            DBProvider dbNew = GetDb();
            try
            {
                dbNew.Begin();
                if (wares != null && wares.Count > 0)
                {
                    foreach (var myProduct in wares)
                    {
                        dbNew.ExecInsert(myProduct, "ProductInfo", new string[]
                        {
                            "RID", "ProductID", "SkuID", "SkuidKey", "VenderId", "ShopId", "ProductName",
                            "BrandID", "ProductBrand", "ProductAttach", "ProductIsSaled", "ProductTag", "Catalog",
                            "ProductImageWebPath",
                            "ProductImagePath", "ProductURL", "ProductCoupon", "ProductPromoMsg",
                            "ProductDispatchMode",
                            "ProductPrice", "ProductMinPrice", "ProductMobilePrice", "ProductQQPrice",
                            "ProductWXPrice",
                            "ProductType", "ProductPriceTrend",
                            "ProductGeneralRate", "ProductPoorRate", "ProductHotCommentTag", "ProductEvaluateCount",
                            "ProductGoodRate",
                            "CreateTime", "CreateUser", "Focus", "BEnable"
                        });
                    }

                }

                dbNew.Commit();

            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                dbNew.Rollback();
            }
        }
        /// <summary>
        /// 获取历史商品价格
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="hisWares"></param>
        /// <returns></returns>
        public List<PriceWebHistory> GetHisWarePrice(string connStr, List<ProductInfo> hisWares)
        {
            string conn = string.Format("Data Source={0};Pooling=true", connStr);
            DBProvider dbHis = GetDb("System.Data.SQLite", conn);
            try
            {
                dbHis.Begin();
                List<PriceWebHistory> hisPriceList = new List<PriceWebHistory>();
                foreach (var item in hisWares)
                {
                    string sql = string.Format("select * from PriceWebHistory where pid = '{0}';", item.ProductID);
                    hisPriceList.AddRange(dbHis.Query<PriceWebHistory>(sql) ?? new WsList<PriceWebHistory>());
                }

                dbHis.Commit();
                return hisPriceList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                dbHis.Rollback();
                return null;
            }
        }
        /// <summary>
        /// 获取历史商品价格
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public List<PriceWebHistory> GetHisWarePrice(string connStr)
        {
            string conn = string.Format("Data Source={0};Pooling=true", connStr);
            DBProvider dbHis = GetDb("System.Data.SQLite", conn);
            try
            {
                dbHis.Begin();
                List<PriceWebHistory> hisPriceList = dbHis.Query<PriceWebHistory>("select * from ProductPriceHistory where Date(PriceDate) != Date('1899-12-30') order by pid,PriceDate;");
                dbHis.Commit();
                return hisPriceList;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                dbHis.Rollback();
                return null;
            }
        }
        /// <summary>
        /// 导入商品历史价格
        /// </summary>
        /// <param name="hisPrices"></param>
        public void ImportHisWarePrice(List<PriceWebHistory> hisPrices)
        {
            DBProvider dbNew = GetDb();
            try
            {
                dbNew.Begin();
                if (hisPrices != null && hisPrices.Count > 0)
                {
                    foreach (var item in hisPrices)
                    {
                        string sql = string.Format(
                                "insert into PriceWebHistory (PID, Price, PriceDate, PriceType) select '{0}',{1},'{2}','{3}';",
                                item.PID, item.Price, item.PriceDate.ToString("yyyy-MM-dd HH:mm:ss"), item.PriceType);
                        dbNew.Exec(sql);
                    }

                }
                dbNew.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                dbNew.Rollback();
            }
        }

        /// <summary>
        /// 导入历史数据
        /// </summary>
        /// <remarks>根据数据文件路径导入</remarks>
        /// <param name="connStr"></param>
        public void HisWareImport(string connStr)
        {
            string conn = string.Format("Data Source={0};Pooling=true", connStr);
            DBProvider dbHis = GetDb("System.Data.SQLite", conn);
            try
            {
                dbHis.Begin();
                List<ProductInfo> hisWareList = dbHis.Query<ProductInfo>("select * from ProductInfo order by CreateTime");
                List<PriceWebHistory> hisPriceList = dbHis.Query<PriceWebHistory>("select * from PriceWebHistory order by PriceDate");
                dbHis.Commit();
                HisWareImport(hisWareList, hisPriceList);
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                dbHis.Rollback();
            }
        }

        /// <summary>
        /// 导入历史数据
        /// </summary>
        /// <param name="hisWares">商品集合</param>
        /// <param name="hisPrices">历史价格集合</param>
        private void HisWareImport(List<ProductInfo> hisWares, List<PriceWebHistory> hisPrices)
        {
            DBProvider dbNew = GetDb();
            try
            {
                dbNew.Begin();
                List<string> wareList = new List<string>();
                if (hisWares != null && hisWares.Count > 0)
                {
                    foreach (var myProduct in hisWares)
                    {
                        string sql = string.Format("select count(*) cou from ProductInfo where ProductID ='{0}'; ",
                            myProduct.ProductID);
                        long iCou = dbNew.QueryOneCol<long>(sql);
                        if (iCou == 0 && myProduct.BEnable)
                        {
                            wareList.Add(myProduct.ProductID);
                            dbNew.ExecInsert(myProduct, "ProductInfo", new string[]
                            {
                                "RID", "ProductID", "SkuID", "SkuidKey", "VenderId", "ShopId", "ProductName",
                                "BrandID", "ProductBrand", "ProductAttach", "ProductIsSaled", "ProductTag", "Catalog",
                                "ProductImageWebPath",
                                "ProductImagePath", "ProductURL", "ProductCoupon", "ProductPromoMsg",
                                "ProductDispatchMode",
                                "ProductPrice", "ProductMinPrice", "ProductMobilePrice", "ProductQQPrice",
                                "ProductWXPrice",
                                "ProductType", "ProductPriceTrend",
                                "ProductGeneralRate", "ProductPoorRate", "ProductHotCommentTag", "ProductEvaluateCount",
                                "ProductGoodRate",
                                "CreateTime", "CreateUser", "Focus", "BEnable"
                            });
                        }

                    }

                }
                if (hisPrices != null && hisPrices.Count > 0)
                {
                    foreach (var item in hisPrices)
                    {
                        string tmpPid = wareList.Find(t => t == item.PID);
                        if (!string.IsNullOrEmpty(tmpPid))
                        {
                            string sql = string.Format("select count(*) cou from PriceWebHistory where pricedate = '{0}' and pid ='{1}' and price={2}; ",
                                item.PriceDate, item.PID, item.Price);
                            long iCou = dbNew.QueryOneCol<long>(sql);
                            if (iCou == 0)
                            {

                                sql = string.Format(
                                    "insert into PriceWebHistory (PID, Price, PriceDate, PriceType) select '{0}',{1},'{2}','{3}'",
                                    item.PID, item.Price, item.PriceDate, item.PriceType);
                                dbNew.Exec(sql);
                            }
                        }

                    }

                }

                dbNew.Commit();

            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                dbNew.Rollback();
            }
        }

        #endregion 导入历史数据

        #region Ware Dispatch Area
        /// <summary>
        /// 清空配送区域数据
        /// </summary>
        public void AreaClear()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = "delete from DispatchArea; ";
                db.Exec(sql);
                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }
        /// <summary>
        /// 刷新配送区域
        /// </summary>
        /// <param name="areas"></param>
        public void AreaAdd(List<DispatchArea> areas)
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                db.Exec("delete from DispatchArea; ");
                foreach (var item in areas)
                {
                    db.ExecInsert(item, "DispatchArea", new string[] { "id", "Name", "ParentID", "Level" });
                }

                db.Commit();
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
            }
        }

        #endregion Ware Dispatch Area

        #region 数据库版本处理

        public bool UpdateDbVersion()
        {
            DBProvider db = GetDb();
            try
            {
                db.Begin();
                string sql = "SELECT COUNT(*) as CNT FROM sqlite_master where type='table' and name='PriceWebHistory'"; //其中DBInfo为需要判断的表名。注意大小写敏感！
                long i = db.QueryOneCol<long>(sql);
                if (i == 0)
                {
                    sql = "alter table ProductPriceHistory rename to PriceWebHistory; ";
                    sql +=
                        "CREATE TABLE [PriceWxHistory] ([PID] CHAR NOT NULL, [Price] DOUBLE NOT NULL DEFAULT 0, [PriceDate] DATETIME NOT NULL DEFAULT (datetime('now','localtime')), " +
                        "[PriceType] VARCHAR2(10) NOT NULL DEFAULT 微信, CONSTRAINT [] PRIMARY KEY ([PID], [Price], [PriceDate])); ";
                    sql +=
                        "CREATE TABLE [PriceQqHistory] ([PID] CHAR NOT NULL, [Price] DOUBLE NOT NULL DEFAULT 0, [PriceDate] DATETIME NOT NULL DEFAULT (datetime('now','localtime')), " +
                        "[PriceType] VARCHAR2(10) NOT NULL DEFAULT QQ, CONSTRAINT [] PRIMARY KEY ([PID], [Price], [PriceDate])); ";
                    sql += "CREATE TABLE [PriceAppHistory] ([PID] CHAR NOT NULL, [Price] DOUBLE NOT NULL DEFAULT 0, [PriceDate] DATETIME NOT NULL DEFAULT (datetime('now','localtime')), " +
                    "[PriceType] VARCHAR2(10) NOT NULL DEFAULT 手机, CONSTRAINT [] PRIMARY KEY ([PID], [Price], [PriceDate])); ";

                    db.Exec(sql);
                }

                db.Commit();
                return true;
            }
            catch (Exception ex)
            {
                Faster.XLogErr(ex.Message);
                db.Rollback();
                return false;
            }
        }

        #endregion 数据库版本处理

        /*delete from pricewebhistory;
delete from priceapphistory;
delete from priceqqhistory;
delete from pricewxhistory;
delete from productinfo;
delete from productMessage;
        */
    }
}
